﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmservices : Form
    {
        public frmservices()
        {
            InitializeComponent();
        }

        private void frmservices_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

            showType();
            autoid();
        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
            sqlType = "select * from t_service";
            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "t_service");

            T_serv.DataSource = ds.Tables["t_service"];
            T_serv.ValueMember = "SERVID";
            T_serv.DisplayMember = "SERVNM";


            string sqlType1;
            sqlType1 = "select * from doctor "; 
            da = new SqlDataAdapter(sqlType1, Conn);
            da.Fill(ds, "doctor");

            dcid.DataSource = ds.Tables["doctor"];
            dcid.ValueMember = "DCID";
            dcid.DisplayMember = "DCNAM";

           
        }
        private void showdata1()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql1 = "select * from person where PNNAM = '" + name.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataReader reader = cmd1.ExecuteReader();
            if (name.Text == "")
            {
                MessageBox.Show("กรุณาป้อนคำที่ต้องการค้นหา !!", "แจ้งเตือน");
                return;
            }
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    HN.Text = reader["HN"].ToString();
                    idcard.Text = reader["IDCARD"].ToString();
                    textBox2.Text = reader["PNNAM"].ToString();
                    bdate.Text = reader["BIRTH"].ToString();
                    add.Text = reader["ADDRESS"].ToString();


                }

                reader.Close();

            }


        }

        private void button13_Click(object sender, EventArgs e)
        {
            if (name.Text == "")
            {
               MessageBox.Show("กรุณากรอกรายชื่อต้องการค้นหา !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                 showdata1();
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

           
            string strdate = Convert.ToString(date1.Value.Year) + "/" + Convert.ToString(date1.Value.Month) + "/" + Convert.ToString(date1.Value.Day);//วันที่
            if (no.Text != "" && name.Text != "" && dist.Text != "")
            {
                string strOut = "";
                strOut += "เลขที่ผู้มาใช้บริการ :" + no.Text + "\n";
                strOut += "ประเภทการบริการ :" + T_serv.SelectedValue + "\n";
                strOut += "ชื่อ-สกุลผู้มาใช้บริการ :" + name.Text + "\n";
                strOut += "วันที่มาใช้บริการ :" + date1.Value.ToLongDateString() + "\n";
                strOut += "แพทย์ผู้ตรวจ :" + dcid.SelectedValue + "\n";
                strOut += "ผลการตรวจ  :" + dist.Text + "\n";


                MessageBox.Show(strOut, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);

                //   try
                //{

                string query = "insert into service values('" + no.Text + "' , '" + T_serv.SelectedValue + "' , '" + HN.Text + "' ,'" + strdate + "','" + dcid.SelectedValue + "' , '" + dist.Text + "')";
                SqlCommand cmd7 = new SqlCommand(query, Conn);
                cmd7.ExecuteNonQuery();


                string sql1 = "SELECT NO,SERVNM,PNNAM,DATE,DCNAM,DIST FROM service,doctor,t_service,person where t_service.SERVID=service.SERVID and doctor.DCID =service.DCID and person.HN= service.HN";
                SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                DataSet ds = new DataSet();

                odbcDA.Fill(ds);

                MessageBox.Show("เพิ่มข้อมูลแล้ว");
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
            autoid();
        }
        private void cleardata()
        {
          
            T_serv.SelectedValue = "";
            name.Text = "";
            dcid.SelectedValue = "";
            dist.Text = ""; 
            idcard.Text = "";
            textBox2.Text = "";
            bdate.Text = "";
            add.Text = "";
        }

        private void button5_Click(object sender, EventArgs e)
        {
            cleardata();
        }

      
        private void button6_Click(object sender, EventArgs e)
        {
            Close();
        }
        private void autoid()
        {
           
            DataTable max = new DataTable();
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT NO FROM service";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            odbcDA.Fill(max);

            DataSet ds7 = new DataSet();
            odbcDA.Fill(ds7, "Repair");
            SqlDataReader reader = cmd1.ExecuteReader();
            
            int max_id = 0;
            string max_data = "";
            if (max.Rows.Count != 0)
            {
                for (int i = 0; i < max.Rows.Count; i++)
                {
                    max_data = max.Rows[i]["NO"].ToString();
                    if (max_id < Convert.ToInt32(max_data))
                    {
                        max_id = Convert.ToInt32(max_data);
                    }
                }
                max_id++;
                max_data = Convert.ToString(max_id);
                if (max_data.Length == 1)
                {
                    // max_data = strdaterep + "0" + max_data;
                    max_data = "000000" + max_data;
                }
                else if (max_data.Length == 2)
                {
                    //max_data = strdaterep + max_data;
                    max_data = "00000" + max_data;
                }
                else if (max_data.Length == 3)
                {
                    max_data = "0000" + max_data;
                    // max_data = "00" + max_data;
                }
                else if (max_data.Length == 4)
                {
                    max_data = "000" + max_data;
                }
                else if (max_data.Length == 5)
                {
                    max_data = "00" + max_data;
                }
                else if (max_data.Length == 6)
                {
                    max_data = "0" + max_data;
                }

                no.Text = max_data;
            }
            else
            {
                no.Text = "0000001";
            }
        }

     
      

       

      
        private void button1_Click(object sender, EventArgs e)
        {
            frmperson f5 = new frmperson();
            f5.ShowDialog();
        }

       
    }
}
